package com.borimi.demo;

import com.borimi.demo.bean.UserBinding;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * JDBC连接MySQL数据库示例类
 */
public class MySQLDatabaseConnector {

    // 数据库连接配置
    private static final String DB_URL = "jdbc:mysql://1.12.104.43:22503/blm_user";
    private static final String DB_USER = "Liuzhangjie";
    private static final String DB_PASSWORD = "xhHfxtUMZ7";

    // 数据库连接对象
    private Connection connection;

    /**
     * 建立数据库连接
     */
    public void connect() throws SQLException {
        try {
            // 加载MySQL JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            System.out.println("数据库连接成功！");
        } catch (ClassNotFoundException e) {
            System.err.println("MySQL JDBC驱动未找到: " + e.getMessage());
            throw new SQLException(e);
        }
    }

    /**
     * 查询数据示例
     */
    public void queryData(String sql) throws SQLException {
        if (connection == null || connection.isClosed()) {
            throw new SQLException("数据库未连接");
        }

        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            // 获取结果集元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 打印列名
            for (int i = 1; i <= columnCount; i++) {
                System.out.print(metaData.getColumnName(i) + "\t");
            }
            System.out.println();

            // 打印数据行
            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.print(resultSet.getString(i) + "\t");
                }
                System.out.println();
            }
        }
    }

    /**
     * 使用PreparedStatement查询数据（防SQL注入）
     */
    public void queryDataWithParams(String sql, Object... params) throws SQLException {
        if (connection == null || connection.isClosed()) {
            throw new SQLException("数据库未连接");
        }

        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            // 设置参数
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                // 获取结果集元数据
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                // 打印列名
                for (int i = 1; i <= columnCount; i++) {
                    System.out.print(metaData.getColumnName(i) + "\t");
                }
                System.out.println();

                // 打印数据行
                while (resultSet.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        System.out.print(resultSet.getString(i) + "\t");
                    }
                    System.out.println();
                }
            }
        }
    }
    /**
     * 查询数据示例，并将结果转换为UserBinding对象列表
     */
    public List<UserBinding> queryUserBindings(String sql) throws SQLException {
        if (connection == null || connection.isClosed()) {
            throw new SQLException("数据库未连接");
        }

        List<UserBinding> userBindings = new ArrayList<>();

        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            // 获取结果集元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 将结果集转换为UserBinding对象列表
            while (resultSet.next()) {
                UserBinding userBinding = new UserBinding();

                // 根据列名映射到UserBinding对象属性
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    String columnClassName = metaData.getColumnClassName(i);

                    // 根据列名映射到对象属性
                    mapColumnToUserBinding(userBinding, columnName, resultSet, i, columnClassName);
                }
                System.out.println();
                userBindings.add(userBinding);
            }
        }

        return userBindings;
    }

    /**
     * 使用PreparedStatement查询数据（防SQL注入），并将结果转换为UserBinding对象列表
     */
    public List<UserBinding> queryUserBindingsWithParams(String sql, Object... params) throws SQLException {
        if (connection == null || connection.isClosed()) {
            throw new SQLException("数据库未连接");
        }

        List<UserBinding> userBindings = new ArrayList<>();

        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            // 设置参数
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                // 获取结果集元数据
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                // 打印列名
                for (int i = 1; i <= columnCount; i++) {
                    System.out.print(metaData.getColumnName(i) + "\t");
                }
                System.out.println();

                // 将结果集转换为UserBinding对象列表
                while (resultSet.next()) {
                    UserBinding userBinding = new UserBinding();

                    // 根据列名映射到UserBinding对象属性
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        String columnClassName = metaData.getColumnClassName(i);

                        // 打印数据行
                        System.out.print(resultSet.getString(i) + "\t");

                        // 根据列名映射到对象属性
                        mapColumnToUserBinding(userBinding, columnName, resultSet, i, columnClassName);
                    }
                    System.out.println();
                    userBindings.add(userBinding);
                }
            }
        }

        return userBindings;
    }

    /**
     * 将列数据映射到UserBinding对象
     */
    private void mapColumnToUserBinding(UserBinding userBinding, String columnName,
                                        ResultSet resultSet, int columnIndex, String columnClassName) throws SQLException {
        // 根据列名映射到UserBinding对象属性
        switch (columnName.toLowerCase()) {
            case "user_id":
                userBinding.setUserId(resultSet.getObject(columnIndex, Integer.class));
                break;
            case "old_user_id":
                userBinding.setOldUserId(resultSet.getObject(columnIndex, Integer.class));
                break;
            case "union_id":
                userBinding.setUnionId(resultSet.getString(columnIndex));
                break;
            case "open_id":
                userBinding.setOpenId(resultSet.getString(columnIndex));
                break;
            case "app_id":
                userBinding.setAppId(resultSet.getString(columnIndex));
                break;
            case "wx_name":
                userBinding.setWxName(resultSet.getString(columnIndex));
                break;
            case "photo_url":
                userBinding.setPhotoUrl(resultSet.getString(columnIndex));
                break;
            case "type":
                userBinding.setType(resultSet.getObject(columnIndex, Integer.class));
                break;
            case "last_login_time":
                userBinding.setLastLoginTime(resultSet.getTimestamp(columnIndex));
                break;
            case "create_time":
                userBinding.setCreateTime(resultSet.getTimestamp(columnIndex));
                break;
            case "update_time":
                userBinding.setUpdateTime(resultSet.getTimestamp(columnIndex));
                break;
            default:
                // 忽略未匹配的列
                break;
        }
    }

    /**
     * 关闭数据库连接
     */
    public void disconnect() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                System.out.println("数据库连接已关闭！");
            }
        } catch (SQLException e) {
            System.err.println("关闭数据库连接时出错: " + e.getMessage());
        }
    }

    /**
     * 获取连接状态
     */
    public boolean isConnected() {
        try {
            return connection != null && !connection.isClosed();
        } catch (SQLException e) {
            return false;
        }
    }
}
